﻿using System;
using System.Reflection;
using System.Collections.Generic;
using System.Collections;
using System.Text;
using System.Xml.Serialization;
using System.Diagnostics;
using System.Data.OleDb;
using System.Data;
using System.IO;

namespace sqlcmd
{
	class Program
	{
		static TextWriter m_spoolFile = null;
		static bool printWithPadding = true;
		static List<string> m_commands = new List<string>();
		static void Main(string[] args)
		{
			
			try
			{
				Settings settings = SettingsReader.Read();
				Parameters parameters = new Parameters(args, settings);
				AssemblyName asn = System.Reflection.Assembly.GetExecutingAssembly().GetName();
				Print(asn.FullName);
				Print("Connecting to " + parameters.ConnectString);
				InitCommands();

				Type t = Type.GetType(parameters.ConnectClass);
				if (t == null)
				{
					throw new ApplicationException(string.Format("Type {0} not found", parameters.ConnectClass));
				}

				//System.Data.Common.DbProviderFactory factory = System.Data.Common.DbProviderFactories.GetFactory("Npgsql");
				//IDbConnection conn = factory.CreateConnection();
				

				IDbConnection conn = (IDbConnection)Activator.CreateInstance(t);
				conn.ConnectionString = parameters.ConnectString;

				conn.Open();
				IDbCommand cmd = conn.CreateCommand();
				if (settings.CommandTimeout > 0)
				{
					cmd.CommandTimeout = settings.CommandTimeout;
				}
				Print("Connected...\n");
				if (!string.IsNullOrEmpty(parameters.DBType.InitString))
				{
					Print("Init connection...\n");
					Execute(cmd, parameters.DBType.InitString);
				}
				
				string sqlBuff = "";
				string lastSqlBuff = "";
				TextReader scriptReader = null;
				if (!string.IsNullOrEmpty(parameters.Script))
				{
					scriptReader = new StreamReader(parameters.Script);
				}
				
				while (true)
				{
					try
					{
						string newline = null;
						if (scriptReader == null)
						{
							newline = Prompt().TrimEnd();
						}
						else
						{
							newline = scriptReader.ReadLine();
							if (newline == null)
							{
								scriptReader.Close();
								scriptReader = null;
								newline = "";
							}
						}
						if (scriptReader == null && sqlBuff == "" && newline.StartsWith("@"))
						{
							scriptReader = new StreamReader(newline.Substring(1));
							sqlBuff = "";
							continue;
						}

						if (IsCommand(newline))
						{
							if (!HandleCommands(settings, ref newline))
							{
								break;
							}
							sqlBuff = "";
							continue;
						}

						if (newline.StartsWith("--"))
						{
							continue;
						}

						if (scriptReader == null && sqlBuff == "" && newline.ToLower() == "l")
						{
							Print(lastSqlBuff);
							continue;
						}

						if (scriptReader == null && sqlBuff == "" && newline.ToLower() == "r")
						{
							sqlBuff = lastSqlBuff;
						}
						else if (sqlBuff == "" && newline.ToLower() == "begin transaction")
						{
							sqlBuff = newline + ";";
						}
						else
						{
							if (sqlBuff != "")
							{
								sqlBuff += "\n" + newline;
							}
							else
							{
								sqlBuff += newline;
							}
						}

						if (sqlBuff.EndsWith(";"))
						{
							try
							{
								Execute(cmd, sqlBuff);
								lastSqlBuff = sqlBuff;
							}
							finally
							{
								sqlBuff = "";
							}
						}
					}
					catch (Exception ex)
					{
						Print(ex.Message);
					}
				}
			}
			catch (Exception ex)
			{
				Print(ex.Message);
			}
		}

		private static bool HandleCommands(Settings settings, ref string newline)
		{
			if (newline.ToLower() == "exit")
			{
				return false;
			}
			else if (newline.StartsWith("set"))
			{
				string[] arr = newline.Split(new char[] { ' ', '=' });
				int newCmdTimeout = 0;
				if (arr.Length == 3 && (arr[1].ToLower() == "timeout" || arr[1].ToLower() == "tm" || arr[1].ToLower() == "commandtimeout"))
				{
					if (int.TryParse(arr[2], out newCmdTimeout))
					{
						settings.CommandTimeout = newCmdTimeout;
						Print(string.Format("CommandTimeout is now {0}", settings.CommandTimeout));
					}
					else
					{
						Print("Invalid value");
					}
				}
				else if (arr.Length == 3 && (arr[1].ToLower() == "padding"))
				{
					if (arr[2].ToLower() == "on")
					{
						printWithPadding = true;
						Print("printWithPadding is on");
					}
					else
					{
						printWithPadding = false;
						Print("printWithPadding is off");
					}
				}
				else
				{
					Print("Unknown set command");
				}
			}
			else if (newline.StartsWith("spool off"))
			{
				Print("spool is off");
				if (m_spoolFile != null)
				{
					m_spoolFile.Close();
				}
				m_spoolFile = null;
			}
			else if (newline.StartsWith("spool"))
			{
				newline = newline.Substring(5);
				if (string.IsNullOrEmpty(newline))
				{
					throw new ApplicationException("No filename supplied");
				}
				Print("spooling to " + newline);
				m_spoolFile = new StreamWriter(new FileStream(newline, FileMode.Create));
			}
			return true;
		}
		

		private static bool IsCommand(string newline)
		{
			foreach (string cmd in m_commands)
			{
				if (newline.StartsWith(cmd))
				{
					return true;
				}
			}
			return false;
		}
		private static void InitCommands()
		{
			m_commands.Add("exit");
			m_commands.Add("set");
			m_commands.Add("spool off");
			m_commands.Add("spool");
		}

		private static void Execute(IDbCommand cmd, string sqlBuff)
		{
			try
			{
				cmd.CommandText = sqlBuff.Remove(sqlBuff.Length - 1);
				ParseSql(sqlBuff);
				if (IsSelect)
				{
					using (IDataReader reader = cmd.ExecuteReader())
					{
						if (printWithPadding)
						{
							PrintWithPadding(reader);
						}
						else
						{
							Print(reader);
						}
					}
				}
				else
				{
					Print("");
					int result = cmd.ExecuteNonQuery();
					if (ReportRows)
					{
						Print(result + " rows affected");
					}
					else
					{
						Print("ok");
					}
					Print("");
				}
			}
			finally
			{
			}
		}
		static bool ReportRows = false;
		static bool IsSelect = false;
		static void ParseSql(string sqlBuff)
		{
			Hashtable ht = new Hashtable();
			ht[sqlBuff.ToLower().IndexOf("select")] = "select";
			ht[sqlBuff.ToLower().IndexOf("update")] = "update";
			ht[sqlBuff.ToLower().IndexOf("insert")] = "insert";
			ht[sqlBuff.ToLower().IndexOf("delete")] = "delete";
			ht[sqlBuff.ToLower().IndexOf("create")] = "create";
			ht[sqlBuff.ToLower().IndexOf("drop")] = "drop";
			ht[sqlBuff.ToLower().IndexOf("alter")] = "alter";
			ht[sqlBuff.ToLower().IndexOf("rollback")] = "rollback";
			ht[sqlBuff.ToLower().IndexOf("commit")] = "commit";
			ht[sqlBuff.ToLower().IndexOf("begin transaction")] = "begin transaction";
			ht.Remove(-1);
			ArrayList a = new ArrayList(ht.Keys);
			a.Sort();
			if (ht.Count <= 0)
			{
				throw new ApplicationException("Unknown command");
			}
			IsSelect = (string)ht[a[0]] == "select";
			ReportRows = IsSelect || (string)ht[a[0]] == "update" || (string)ht[a[0]] == "insert" || (string)ht[a[0]] == "delete";
		}
		static string Prompt()
		{
			Console.Write(" > ");
			return Console.ReadLine();
		}

		static void Print(IDataReader reader)
		{
			int rows = 0;
			string scols = "";
			for (int cols = 0; cols < reader.FieldCount; cols++)
			{
				scols += reader.GetName(cols) + "  ";
			}
			Print("");
			Print(scols);
			Print("");
			while (reader.Read())
			{
				rows++;
				string line = "";
				for (int col = 0; col < reader.FieldCount; col++)
				{
					line += "  " + reader.GetValue(col).ToString();
				}
				Print(line);
			}
			Print(rows.ToString() + " rows");
			Print("");
		}
		static void PrintWithPadding(IDataReader reader)
		{
			int rows = 0;
			string scols = "";
			List<int> colLen = new List<int>();
			List<bool> colnumeric = new List<bool>();
			for (int cols = 0; cols < reader.FieldCount; cols++)
			{
				bool numeric = false;
				string tn = reader.GetDataTypeName(cols);
				string name = reader.GetName(cols);
				int len = reader.GetName(cols).Length + 2;
				Type t = reader.GetFieldType(cols);
				if (t == System.Type.GetType("System.Decimal"))
				{
					len = 20;
					numeric = true;
				}
				else if (t == System.Type.GetType("System.Double"))
				{
					len = 20;
					numeric = true;
				}
				else if (t == System.Type.GetType("System.Int32"))
				{
					len = 10;
					numeric = true;
				}
				else if (t == System.Type.GetType("System.Int64"))
				{
					len = 15;
					numeric = true;
				}
				else if (t == System.Type.GetType("System.Object"))
				{
					len = 15;
				}
				if (len > 40 )
				{
					len = 40;
				}
				colnumeric.Add(numeric);
				colLen.Add(len);
			}
			
			for (int cols = 0; cols < reader.FieldCount; cols++)
			{
				if (colnumeric[cols])
				{
					scols += string.Format(" {0} ", reader.GetName(cols)).PadLeft(colLen[cols]);
				}
				else
				{
					scols += string.Format(" {0} ", reader.GetName(cols)).PadRight(colLen[cols]);
				}
				
			}
			Print("");
			Print(scols);
			Print("");
			while (reader.Read())
			{
				rows++;
				string line = "";
				for (int col = 0; col < reader.FieldCount; col++)
				{
					object v = reader.GetValue(col);
					string vs = v.ToString();
					if (v is System.DBNull )
					{
						vs = "<n>";
					}
					//if (v.GetType() == Type.GetType( "System.Object" ))
					//{
					//   vs = v.ToString().sub
					//}
					if (colnumeric[col])
					{
						line += string.Format(" {0} ", vs.ToString()).PadLeft((colLen[col]));
					}
					else
					{
						line += string.Format(" {0} ", vs.ToString()).PadRight((colLen[col]));
					}
				}
				Print(line);
			}
			Print(rows.ToString() + " rows");
			Print("");
		}
		static void Print(string msg)
		{
			Console.WriteLine(msg);
			Debug.WriteLine(msg);
			if (m_spoolFile != null)
			{
				m_spoolFile.WriteLine(msg);
			}
		}
	}
}
